/*-----------------------------------------------------------------------------------
 *
 * 프로그램명	: HomePageDAO.java
 * Version		: 1.0
 * 작성일 		: 2006-08-16
 * 작성자		:
 * 수정일 		:
 * 수정자		:
 * 설명		 	:
 * 기능		 	:
 *
 * Copyright (c) 2000 by H&H  All Rights Reserved.
 *
-------------------------------------------------------------------------------------*/
package com.welfare.admin.call;


import java.util.*;
import java.sql.*;

import oracle.jdbc.driver.OracleResultSet;

import com.welfare.common.CommonDAO;
import com.welfare.common.CommonUtil;
import com.zll.framework.db.DBManager;
import com.zll.framework.model.HashBox;
import com.zll.framework.model.HashList;
import com.zll.framework.util.StringUtil;



public class CallDAO
{
	public HashList searchCustomer(HashMap infos) throws Exception
	{
        return DBManager.executeQuery("/call.xml", "search_customer", infos);
	}

	public HashList selectCustomerInfo(HashMap infos) throws Exception
	{
        return DBManager.executeQuery("/call.xml", "select_customer_info", infos);
	}
	
	public HashList selectCustomerList(HashMap infos) throws Exception
	{
        return DBManager.executeQueryByPaging("/call.xml", "select_customer_list", infos);
	}
	
	public HashList selectCounselList(HashMap infos) throws Exception
	{
        return DBManager.executeQuery("/call.xml", "select_counsel_list", infos);
	}

	public HashList selectCounselInfo(HashMap infos) throws Exception
	{
        return DBManager.executeQuery("/call.xml", "select_counsel_info", infos);
	}

	public HashList selectManualList(HashMap infos) throws Exception
	{
        return DBManager.executeQuery("/call.xml", "select_manual_list", infos);
	}

	public HashList selectManualInfo(HashMap infos) throws Exception
	{
        return DBManager.executeQuery("/call.xml", "select_manual_info", infos);
	}
	
	public void updateManualHitCount(HashMap infos) throws Exception
	{
		DBManager.executeUpdate("/call.xml", "update_manual_hit_count", infos);
	}

	public HashList selectSmsList(HashMap infos) throws Exception
	{
        return DBManager.executeQuery("/call.xml", "select_sms_list", infos);
	}

	public HashList selectSmsInfo(HashMap infos) throws Exception
	{
        return DBManager.executeQuery("/call.xml", "select_sms_info", infos);
	}
	
	

	public HashList selectCounselAnalysis(HashMap infos) throws Exception
	{
		HashList code305 = (HashList)infos.get("code305");	//대분류
		HashList code306 = (HashList)infos.get("code306");	//소분류
		
		String sql = "";
		
		if ("1".equals(infos.get("counsel_kind"))) {
			for (int i = 0; i < code305.size(); i++) {
				String counsel_kind = code305.get("code",i);
				sql += "\n, sum(case when a.counsel_kind = '"+counsel_kind+"' then 1 else 0 end) as sum_"+counsel_kind;
			}
		}
		else {
			for (int i = 0; i < code305.size(); i++) {
				String counsel_kind = code305.get("code",i);
				//sql += "\n, sum(case when a.counsel_kind1 = '"+counsel_kind1+"' then 1 else 0 end) as sum"+counsel_kind1;
				for (int j = 0; j < code306.size(); j++) {
					if (counsel_kind.equals(code306.get("code_desc",j))) {
						sql += "\n, sum(case when a.counsel_kind = '"+counsel_kind+"' and a.counsel_part = '"+code306.get("code",j)+"' then 1 else 0 end) as sum_"+code306.get("code",j);
					}
				}
			}
		}
		
		infos.put("dynamic_sql", sql);
		
        return DBManager.executeQuery("/call.xml", "select_counsel_analysis", infos);
	}
	
	/**
	 * 고객 저장
	 *
	 * @param	infos
	 * @return	boolean
	 */
	public String saveCustomer(HashBox box) throws Exception
    {
		Connection conn = null;
		PreparedStatement   pstmt = null;
		ResultSet rs = null;
		String customerId = "";
		
        try
		{
			conn = DBManager.getConnection();
            

			HashMap param = box.getParam();
			

			if ("D".equals(param.get("job_type"))) {
	       		DBManager.executeUpdate("/call.xml", "delete_customer", param, conn);
	       		DBManager.executeUpdate("/call.xml", "delete_customer_counsel", param, conn);
	        } 
			else if ("C".equals(param.get("job_type"))) {
				customerId = DBManager.executeQuery("/call.xml", "select_customer_id", param).get("customer_id", 0);
				param.put("customer_id", customerId);
				DBManager.executeUpdate("/call.xml", "insert_customer", param, conn);
			} 
			else if ("U".equals(param.get("job_type"))) {
				customerId = box.get("customer_id");
				DBManager.executeUpdate("/call.xml", "update_customer", param, conn);
				DBManager.executeUpdate("/call.xml", "update_customer_counsel", param, conn);	//상담이력의 고객정보(성명, 생년월일, 성별)을 수정한다.
	        }

			conn.commit();
			
			return customerId;
	    }
	    catch(Exception e)
	    {
	    	DBManager.rollback(conn);
	    	throw e;
		}
	    finally
	    {
	    	DBManager.closeConnection(pstmt);
	    	DBManager.closeConnection(conn);
		}
    }

	
	/**
	 * 상담 저장
	 *
	 * @param	infos
	 * @return	boolean
	 */
	public void saveCounsel(HashBox box) throws Exception
    {
		Connection conn = null;
		PreparedStatement   pstmt = null;

		
        try
		{

        	conn = DBManager.getConnection();
            

			HashMap param = box.getParam();
			

			if ("D".equals(param.get("job_type"))) {
	       		DBManager.executeUpdate("/call.xml", "delete_counsel", param, conn);
	        } 
			else if ("C".equals(param.get("job_type"))) {
				DBManager.executeUpdate("/call.xml", "insert_counsel", param, conn);
			} 
			else if ("U".equals(param.get("job_type"))) {
				DBManager.executeUpdate("/call.xml", "update_counsel", param, conn);
	        }

			conn.commit();
	    }
	    catch(Exception e)
	    {
	    	DBManager.rollback(conn);
	    	throw e;
		}
	    finally
	    {
	    	DBManager.closeConnection(pstmt);
	    	DBManager.closeConnection(conn);
		}
    }
	
	
	/**
	 * 업무메뉴얼 저장
	 *
	 * @param	infos
	 * @return	boolean
	 */
	public String saveManual(HashBox box) throws Exception
    {
		Connection conn = null;
		PreparedStatement   pstmt = null;
		ResultSet rs = null;
		String manualId = "";
		
        try
		{

        	conn = DBManager.getConnection();
            //conn = DBManager.getConnectionByDriver();

			HashMap param = box.getParam();
			
			String sql = "select content_info from wfr_manual where manual_id = ?";

			if ("D".equals(param.get("job_type"))) {
	       		DBManager.executeUpdate("/call.xml", "delete_manual", param, conn);
	        } 
			else if ("C".equals(param.get("job_type"))) {
				manualId = DBManager.executeQuery("/call.xml", "select_manual_id", param).get("manual_id", 0);
				param.put("manual_id", manualId);
				DBManager.executeUpdate("/call.xml", "insert_manual", param, conn);
				
				// Write CLOB Data
	    		pstmt = conn.prepareStatement(sql);
	    		pstmt.setString(1, manualId);
	    		rs = pstmt.executeQuery();
	    		
	            if (rs.next()) {
	            	CommonUtil.updateCLOB(((OracleResultSet)rs).getCLOB("content_info"), StringUtil.nvl(param.get("content_info")));
	            }
			} 
			else if ("U".equals(param.get("job_type"))) {
				DBManager.executeUpdate("/call.xml", "update_manual", param, conn);
				
				// Write CLOB Data
	    		pstmt = conn.prepareStatement(sql);
	    		pstmt.setString(1, (String)param.get("manual_id"));
	    		rs = pstmt.executeQuery();
	    		
	            if (rs.next()) {
	            	CommonUtil.updateCLOB(((OracleResultSet)rs).getCLOB("content_info"), StringUtil.nvl(param.get("content_info")));
	            }
	        }
			
			//첨부파일목록 
			HashList attachFileList = box.getAttachFileList();
			if (attachFileList != null) {
				for (int i = 0; i < attachFileList.size(); i++) {
					DBManager.executeUpdate("/common.xml", "insert_file", (HashMap)attachFileList.get(i), conn);
				}
			}
			
			conn.commit();
			
			return manualId;
	    }
	    catch(Exception e)
	    {
	    	DBManager.rollback(conn);
	    	throw e;
		}
	    finally
	    {
	    	DBManager.closeConnection(pstmt);
	    	DBManager.closeConnection(conn);
		}
    }
	
	
	/**
	 * SMS 발송
	 * 
	 * 
	 * @throws Exception
	 */
	public void saveSms(HashMap param) throws Exception
	{
		Connection conn = null;
		CallableStatement cstmt = null;
		int idx = 1;
		
		/* 프로시저 인자값
		   	i_sendtype       VARCHAR2(10)        -- 발송구분, sms/mms
			i_userid         VARCHAR2(20)        -- 발송자아이디
			i_subject        VARCHAR2(100)       -- 제목
			i_address        VARCHAR2(3000)      -- 수신자명^수신자번호|, 홍길동^01012341234|홍길순^01112341234|, 수신자번호는숫자만으로구성
			i_content        VARCHAR2(1000)      -- SMS내용, VMS내용(tts)
			i_callback_no    VARCHAR2(20)        -- 회신번호, 회신번호는숫자만으로구성
			i_schedule_type  VARCHAR2(1)         -- 0:즉시발송,1:예약발송
			i_schedule_stime VARCHAR2(12)        -- 예약발송시 발송년월일시분, 200801020304
			i_ingam_sendid   VARCHAR2(50) := ''  -- 홈페이지발송고유번호
			i_category_nm    VARCHAR2(50) := ''  -- 발송업무구분명
		 */

		try {
			conn = DBManager.getConnection();
		    cstmt = conn.prepareCall( "{call SP_SEND_SMS(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}" ); 
		    cstmt.setString(idx++, StringUtil.nvl(param.get("sms_gubn")));    	//발송구분 
		    cstmt.setString(idx++, "1004call");									//발송자아이디
		    cstmt.setString(idx++, StringUtil.nvl(param.get("subject")));		//제목
		    cstmt.setString(idx++, StringUtil.nvl(param.get("dest_info")));		//수신자번호
		    cstmt.setString(idx++, StringUtil.nvl(param.get("sms_msg")));		//내용
		    cstmt.setString(idx++, StringUtil.nvl(param.get("callback_no")));	//회신번호
		    cstmt.setString(idx++, "0");										//즉시발송
		    cstmt.setString(idx++, "");											
		    cstmt.setString(idx++, StringUtil.nvl(param.get("customer_id")));	//고객번호
		    cstmt.setString(idx++, "1004call");									//발송업무구분명
		    cstmt.executeQuery();
		 
		} catch ( Exception e ) {
			throw e;
		} finally {
		    cstmt.close();   
		    conn.close();
		}
	}
}
